<?php 
$PageSecurity = 2;
include_once ('includes/session.inc');

$typeArr = array(2,3); // 1 ca sy, 2 nhac sy, 3:ca nhac si
$pageSize = 10;
if(isset($_GET['action'])){
	$action = $_GET['action'];
}else{
	$action = isset($_POST['action']) ? $_POST['action'] : 'get';
}

if($action == 'get'){
	$total = 0;
	$limit = isset($_POST['limit']) ? $_POST['limit'] : $pageSize;
	$start = isset($_POST['start']) ? $_POST['start'] : 0;

	$objectConditions = array();	
	getConditions($objectConditions, 'locked', '=', '0');
	getConditions($objectConditions, 'object_number', 'like', $_POST['srch_object_number']);
	getConditions($objectConditions, 'object_name', 'like', $_POST['srch_object_name']);
	getConditions($objectConditions, 'object_company', 'like', $_POST['srch_object_company']);
	getConditions($objectConditions, 'object_address', 'like', $_POST['srch_object_address']);
	if(isset($_POST['srch_object_type']) && !empty($_POST['srch_object_type'])) {
		getConditions($objectConditions, 'object_type', '=', $_POST['srch_object_type']);
	}else{
		getConditions($objectConditions, 'object_type', 'IN', '(2,3)');
	}
	$contractConditions = array();	
	getConditions($contractConditions, 'contract_number', 'like', $_POST['srch_contract_number']);
	getConditions($contractConditions, 'from_date', '>', $_POST['srch_from_contract_date']);
	getConditions($contractConditions, 'to_date', '<', $_POST['srch_to_contract_date']);
	if(isset($_POST['srch_check_contract']) && !empty($_POST['srch_check_contract'])) {
		if($_POST['srch_check_contract'] == 1) {
			getConditions($contractConditions, 'contract_id', 'IS NOT', 'NULL');
		}elseif($_POST['srch_check_contract'] == 2){
			getConditions($contractConditions, 'contract_id', 'IS', 'NULL');
		}
	}
		
	$result = _single('api/object')->getObjectPaginates($limit, $start, $total, $objectConditions, $contractConditions, $_SESSION['UserID'], $_SESSION['Password']);
	
	
	$data = '{"success":true';
	$data .= ',"data":[';	
	$haveData = false;
	
	while($row = DB_fetch_array($result)) {
	 	$haveData = true;
		$data .= sprintf('{"object_id":"%s","object_number":"%s","object_name":"%s","object_company":"%s",
			"object_address":"%s", "object_phone":"%s", "object_mobile":"%s", "object_email":"%s", 
			"object_fax":"%s", "object_type":"%s", "contract_id":"%s",
			"contract_number":"%s","contract_date":"%s","contract_end":"%s"},', 
			$row['object_id'], 
	 		$row['object_number'], 
	 		$row['object_name'],
	 		$row['object_company'], 
	 		$row['object_address'], 
	 		$row['object_phone'], 
	 		$row['object_mobile'], 
	 		$row['object_email'], 
	 		$row['object_fax'], 
	 		$row['object_type'], 
	 		$row['contract_id'],
			$row['contract_number'],
			($row['contract_date'] == '01/01/1900') ? '' : $row['contract_date'],
			($row['contract_end'] == '01/01/1900') ? '' : $row['contract_end']);
	};
	if($haveData) $data = substr($data, 0, strlen($data) -1);
	$data .= '],"count":"' . json_encode($total) . '"}';
	echo $data;
}elseif($action == 'insert'){	
	//Get datas:
    $ObjectDetails = array();    
	if(isset($_POST['object_number']) && !empty($_POST['object_number'])) $ObjectDetails['object_number'] = DB_escape_string($_POST['object_number']);
	if(isset($_POST['object_name']) && !empty($_POST['object_name'])) $ObjectDetails['object_name'] = DB_escape_string($_POST['object_name']);
	if(isset($_POST['object_company']) && !empty($_POST['object_company'])) $ObjectDetails['object_company'] = DB_escape_string($_POST['object_company']);
	if(isset($_POST['object_address']) && !empty($_POST['object_address'])) $ObjectDetails['object_address'] = DB_escape_string($_POST['object_address']);
	if(isset($_POST['object_phone']) && !empty($_POST['object_phone'])) $ObjectDetails['object_phone'] = $_POST['object_phone'];
	if(isset($_POST['object_mobile']) && !empty($_POST['object_mobile'])) $ObjectDetails['object_mobile'] = $_POST['object_mobile'];
	if(isset($_POST['object_email']) && !empty($_POST['object_email'])) $ObjectDetails['object_email'] = $_POST['object_email'];
	if(isset($_POST['object_type']) && !empty($_POST['object_type'])) $ObjectDetails['object_type'] = $_POST['object_type'];
	$Contract = array();   
	if(isset($_POST['contract_number']) && !empty($_POST['contract_number'])) $Contract['contract_number'] = DB_escape_string($_POST['contract_number']);
	if(isset($_POST['contract_date']) && !empty($_POST['contract_date'])) $Contract['contract_date'] = $_POST['contract_date'];
	if(isset($_POST['contract_end']) && !empty($_POST['contract_end'])) $Contract['contract_end'] = $_POST['contract_end'];
	
	$result = array();
	$checkObjNumber =  _single('api/object')->verifyFieldExist('object_number',$ObjectDetails['object_number']);
	$result['error'] = 0;
	if($checkObjNumber) {
		$result['error'] = 1;
	}else{
		$object_id  = _single('api/object')->insert($ObjectDetails, $_SESSION['UserID'], $_SESSION['Password']);	
		if($object_id){
			$result['object_id'] = $object_id;
			if(!empty($_POST['contract_number'])) {
				$Contract['object_id'] = $object_id;
				$Contract['contract_type'] = 2;//tam thoi chua quan ly
				$Contract['contract_inactive'] = 0;
				$contract_id = _single('api/contract')->insert($Contract, $_SESSION['UserID'], $_SESSION['Password']);
				$result['contract_id'] = $contract_id;				
			}
		};
	};
	
	echo '{"success":true,"data":'. json_encode($result).'}';
}elseif($action == 'update') {
	//Execute
	$result = array();
	$result['error'] = 0;
	if($_POST['field'] == 'object_number') {		
		$checkObjNumber =  _single('api/object')->verifyFieldExist('object_number',DB_escape_string(trim($_POST['value'])));		
		if($checkObjNumber) {
			$result['error'] = 1;
		}else{
			$result['data'] = _single('api/object')->update($_POST['object_id'], $_POST['field'], DB_escape_string(trim($_POST['value'])));
		}
	}else{		
		$result['data'] = _single('api/object')->update($_POST['object_id'], $_POST['field'], DB_escape_string(trim($_POST['value'])));
	}
	//Response	
	echo '{"success":true ,"data":'. json_encode($result).'}';
	
}elseif($action == 'delete') {
	$ObjectDetails = array();    	
	$delete_rs = _single('api/object')->delete($_POST['object_id'], $_SESSION['UserID'], $_SESSION['Password']);
	if($delete_rs === false){
		echo '{"success":false ,"data":'. json_encode(null).'}';
	}else{
		echo '{"success":true ,"data":'. json_encode($_POST['object_id']).'}';
	}	
}elseif($_GET['action'] == 'export') {
	
	$DEFAULT_EXPORT_FILE_NAME = 'DanhSachNhacSi';
    $DEFAULT_DIR_UPLOAD = 'tmp/exported/';
    $DEFAULT_EXCEL_TYPE = 'Excel5'; // default Excel 2003 - Excel5,Excel2007
    
    $objPHPExcel = new Api_Excel($DEFAULT_EXPORT_FILE_NAME.'_'.date('YmdHis'), $DEFAULT_DIR_UPLOAD, $DEFAULT_EXCEL_TYPE);
    $objPHPExcel->setActiveSheet(0);
	
	$aligment_HorCent_VerCent = array (
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
	);
	
	$aligment_HorCent_VerBottom = array (
		'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
		'vertical' => PHPExcel_Style_Alignment::VERTICAL_BOTTOM
	);
	
    $border_thin = array (
		'style' => PHPExcel_Style_Border :: BORDER_THIN			
	);
    $border_thin_ALL_title = array(
		'borders'=>array(
			'top'=>$border_thin, 
			'right'=>$border_thin, 
			'bottom'=>$border_thin, 
			'left'=>$border_thin
		),
		'alignment' => $aligment_HorCent_VerCent,
		'font' => array (
			'bold' => true,
			'size' => 12
		)
	);
	
	$border_thin_ALL = array(
		'borders'=>array(
			'top'=>$border_thin, 
			'right'=>$border_thin, 
			'bottom'=>$border_thin, 
			'left'=>$border_thin
		),
		'alignment' => $aligment_HorCent_VerBottom,
		'font' => array (
			'bold' => false,
			'size' => 12
		)
	);
    
	// set default style	
	$objPHPExcel->setPageSetup(85, PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4, PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
	$objPHPExcel->setDefaultStyle('Times New Roman', 10);
	//$objPHPExcel->setFreezePane("E13");
	$objPHPExcel->setPageMargins(0.5, 0.25, 0, 0.75);
	$objPHPExcel->setHorizontalCentered(true);		
		
	$objPHPExcel->setColumnWidth("A", 9)
				->setColumnWidth("B", 10)
				->setColumnWidth("C", 30)
				->setColumnWidth("D", 25)
				->setColumnWidth("E", 15)
				->setColumnWidth("F", 20)
				->setColumnWidth("G", 30)
				->setColumnWidth("H", 20)
				->setColumnWidth("I", 20)
				->setColumnWidth("J", 20)
				->setColumnWidth("K", 20)
				->setColumnWidth("L", 20)
				->setColumnWidth("M", 20)
				->setColumnWidth("N", 20)
				;
	$objPHPExcel->setText("A1","Stt")
				->setText("B1","Mã nhạc sĩ")
				->setText("C1","Tên nhạc sĩ")
				->setText("D1","Tên thật")
				->setText("E1","Ca/Nhạc sỹ")
				->setText("F1","Công ty")
				->setText("G1","Số hợp đồng")
				->setText("H1","Ngày ký kết HĐ")
				->setText("I1","Ngày kết thúc HĐ")	
				->setText("J1","Địa chỉ")	
				->setText("K1","Điện thoại")	
				->setText("L1","Di động")
				->setText("M1","Email")	
				->setDuplicateStyleArray($border_thin_ALL_title, "A1", "A1")
				->setDuplicateStyleArray($border_thin_ALL_title, "B1", "B1")
				->setDuplicateStyleArray($border_thin_ALL_title, "C1", "C1")
				->setDuplicateStyleArray($border_thin_ALL_title, "D1", "D1")
				->setDuplicateStyleArray($border_thin_ALL_title, "E1", "E1")
				->setDuplicateStyleArray($border_thin_ALL_title, "F1", "F1")
				->setDuplicateStyleArray($border_thin_ALL_title, "G1", "G1")
				->setDuplicateStyleArray($border_thin_ALL_title, "H1", "H1")
				->setDuplicateStyleArray($border_thin_ALL_title, "I1", "I1")
				->setDuplicateStyleArray($border_thin_ALL_title, "J1", "J1")
				->setDuplicateStyleArray($border_thin_ALL_title, "K1", "K1")
				->setDuplicateStyleArray($border_thin_ALL_title, "L1", "L1")
				->setDuplicateStyleArray($border_thin_ALL_title, "M1", "M1")
				;
				
	$objectConditions = array();	
	getConditions($objectConditions, 'locked', '=', '0');
	getConditions($objectConditions, 'object_number', 'like', $_GET['ob']);
	getConditions($objectConditions, 'object_name', 'like', $_GET['on']);
	getConditions($objectConditions, 'object_company', 'like', $_GET['oc']);
	getConditions($objectConditions, 'object_address', 'like', $_GET['oa']);
	if(isset($_GET['ot']) && !empty($_GET['ot'])) {
		getConditions($objectConditions, 'object_type', '=', $_GET['ot']);
	}else{
		getConditions($objectConditions, 'object_type', 'IN', '(2,3)');
	}
	$contractConditions = array();	
	getConditions($contractConditions, 'contract_number', 'like', $_GET['cn']);
	getConditions($contractConditions, 'from_date', '>', $_GET['fcd']);
	getConditions($contractConditions, 'to_date', '<', $_GET['tcd']);
	if(isset($_GET['cc']) && !empty($_GET['cc'])) {
		if($_GET['cc'] == 1) {
			getConditions($contractConditions, 'contract_id', 'IS NOT', 'NULL');
		}elseif($_GET['cc'] == 2){
			getConditions($contractConditions, 'contract_id', 'IS', 'NULL');
		}
	}
		
	$result = _single('api/object')->getObjectPaginates(null, null, $total, $objectConditions, $contractConditions, $_SESSION['UserID'], $_SESSION['Password']);
	
	$Position = 2;
	$Stt = 1;
	while ($row = DB_fetch_array($result)) {
		
		$objPHPExcel->setDuplicateStyleArray($border_thin_ALL, "A".($Position), "A$Position")
					->setDuplicateStyleArray($border_thin_ALL, "B".($Position), "B$Position")
					->setDuplicateStyleArray($border_thin_ALL, "C".($Position), "C$Position")
					->setDuplicateStyleArray($border_thin_ALL, "D".($Position), "D$Position")
					->setDuplicateStyleArray($border_thin_ALL, "E".($Position), "E$Position")
					->setDuplicateStyleArray($border_thin_ALL, "F".($Position), "F$Position")
					->setDuplicateStyleArray($border_thin_ALL, "G".($Position), "G$Position")
					->setDuplicateStyleArray($border_thin_ALL, "H".($Position), "H$Position")
					->setDuplicateStyleArray($border_thin_ALL, "I".($Position), "I$Position")
					->setDuplicateStyleArray($border_thin_ALL, "J".($Position), "J$Position")
					->setDuplicateStyleArray($border_thin_ALL, "K".($Position), "K$Position")
					->setDuplicateStyleArray($border_thin_ALL, "L".($Position), "L$Position")
					->setDuplicateStyleArray($border_thin_ALL, "M".($Position), "M$Position")	
					->setText("A$Position",$Stt)
					->setText("B$Position",$row['object_number'])
					->setText("C$Position",$row['object_name'])
					->setText("D$Position",$row['object_real_name'])
					->setText("E$Position",($row['object_type']==3)?'x':'')
					->setText("F$Position",$row['object_company'])
					->setText("G$Position",$row['contract_number'])
					->setText("H$Position",$row['contract_date'])
					->setText("I$Position",$row['contract_end'])
					->setText("J$Position",$row['object_address'])
					->setText("K$Position",$row['object_phone'])
					->setText("L$Position",$row['object_mobile'])
					->setText("M$Position",$row['object_email'])
					->setCellHorizontalAllignment("C".($Position), 'left')
					->setCellHorizontalAllignment("F".($Position), 'left')
					->setCellHorizontalAllignment("J".($Position), 'left')
					->setCellHorizontalAllignment("K".($Position), 'left')
					->setCellHorizontalAllignment("L".($Position), 'left')
					->setCellHorizontalAllignment("M".($Position), 'left')
					;
		$Stt++;
		$Position++;
	}
	$objPHPExcel->outputToBrowser();
	exit;
}elseif($action == 'insertContract'){
	//Get datas:	
	$Contract = array();   
	if(isset($_POST['contract_number']) && !empty($_POST['contract_number'])) $Contract['contract_number'] = DB_escape_string($_POST['contract_number']);
	if(isset($_POST['contract_date']) && !empty($_POST['contract_date'])) $Contract['contract_date'] = $_POST['contract_date'];
	if(isset($_POST['contract_end']) && !empty($_POST['contract_end'])) $Contract['contract_end'] = $_POST['contract_end'];
		
	$result = array();
	$checkExisted =  _single('api/contract')->verifyFieldExist('contract_id',$contract_id);
	$result['error'] = 0;
	if($checkExisted) {
		$result['error'] = 1;
	}else{
		$Contract['object_id'] = $_POST['object_id'];
		$Contract['contract_type'] = 1;//tam thoi chua quan ly
		$Contract['contract_inactive'] = 0;
		$result['contract_id'] = _single('api/contract')->insert($Contract, $_SESSION['UserID'], $_SESSION['Password']);
	};	
	echo '{"success":true,"data":'. json_encode($result).'}';
}elseif($action == 'updateContract'){	
	$contract_id = $_POST['contract_id'];
	$field = $_POST['field'];
	$value = DB_escape_string(trim($_POST['value']));
	$result = _single('api/contract')->update($contract_id, $field, $value);	
	if($result)	{
		echo '{"success":true,"data":'. json_encode(1).'}';
	}else{
		echo '{"success":false,"data":'. json_encode(1).'}';
	}
};

function getConditions (&$conditions, $field, $relation, $value) {
	if(isset($value) && (!empty($value) || $field=='locked')) {
		$value = ($relation == 'like') ? "N'%".DB_escape_string(trim($value))."%'" : DB_escape_string(trim($value));
		if($field == 'from_date' || $field == 'to_date') {
			$field = 'contract_date';
			$value = '\''.$value.'\'';
		};
		array_push($conditions, array('field' => $field, 'relation' => $relation, 'value' =>  $value));
	};
};
?>